from python.db.until.GetConnectDb import get_sql_connect

# 模拟  数据库中   Crud   创建表   事务  sql注入

"""
    1、sql注入问题的测试 
    2、sql 注入 避免  占位符
"""


def login_sql(username, password):
    con = get_sql_connect()
    cursor = con.cursor()
    sql = "select * from user where user_name = '" + username + "' and user_password = '" + password + "'"

    print(sql)
    cursor.execute(sql)
    print(cursor.fetchall())
    cursor.close()
    con.close()


def login_sql_jj(username, password):
    con = get_sql_connect()
    cursor = con.cursor()
    sql = "select * from user where user_name = %s and user_password = %s"

    print(sql)
    data = (username, password)
    cursor.execute(sql, data)
    print(cursor.fetchall())
    cursor.close()
    con.close()


"""
    增  删  改  提交 事务
    
    单行添加
"""


def insert_data(username, password, role):
    con = get_sql_connect()
    cursor = con.cursor()
    sql = "INSERT INTO `hotel`.`user` (`user_name`, `user_password`, `user_role`) VALUES (%s, %s, %s)"

    try:
        data = (username, password, role)
        cursor.execute(sql, data)
        con.commit()
        print('插入成功')
    except:
        print('插入失败')
    cursor.close()
    con.close()


"""
    多行插入
"""


def insert_data_more(data):
    con = get_sql_connect()
    cursor = con.cursor()
    sql = "INSERT INTO `hotel`.`user` (`user_name`, `user_password`, `user_role`) VALUES (%s, %s, %s)"

    try:
        cursor.executemany(sql, data)
        con.commit()
        print('插入成功')
    except:
        print('插入失败')
    cursor.close()
    con.close()


"""
    实现删除 操作  删除某一个记录
    
"""


def delete_by_id_info(id):
    con = get_sql_connect()
    cursor = con.cursor()
    sql = "delete from user where user_id = %s"

    try:
        cursor.execute(sql, (id,))
        con.commit()
        print('删除成功')
    except:
        print('删除失败')
    cursor.close()
    con.close()


"""
    实现修改 操作  修改某一个记录

"""


def update_by_id_info(username, id):
    con = get_sql_connect()
    cursor = con.cursor()
    sql = "update user set user_name = %s where user_id = %s"

    try:
        cursor.execute(sql, (username, id,))
        con.commit()
        print('修改成功')
    except:
        print('修改失败')
    cursor.close()
    con.close()


def transaction_mysql(zzp_id, szp_id, money):
    con = None
    try:
        con = get_sql_connect()
        cursor = con.cursor()
        sql1 = "update user set user_role = user_rle - %s where user_id = %s"
        sql2 = "update user set user_role = user_role + %s where user_id = %s"
        cursor.execute(sql1, (money, zzp_id))
        cursor.execute(sql2, (money, szp_id))

        con.commit()
        print('success')
    except:
        con.rollback()
        print('error')


if __name__ == '__main__':
    # login_sql('hanamaki', 'admin')
    # login_sql(" ' or ' 1=1 ", " ' or ' 1=1 ")

    # login_sql_jj('hanamaki', 'admin')
    # login_sql_jj(" ' or ' 1=1 ", " ' or ' 1=1 ")

    # insert_data('hanamaki123', '111111', '1')

    # list = [
    #     ('bella', '123456', 1),
    #     ('bella1', '123456', 2),
    #     ('bella2', '123456', 3),
    # ]
    # insert_data_more(list)

    # delete_by_id_info(10)

    # update_by_id_info('hanamaki', 9)

    transaction_mysql(9, 8, 1500)
